﻿using System;
using System.Collections.Generic;
using System.Text;

namespace YougeBookmark.Utilities
{
    /// <summary>
    /// 分页处理工具类（支持复杂查询条件）
    /// </summary>
    public class PaginationHelper
    {
        // 基本分页参数
        private int _currentPage = 1;
        private int _pageSize = 20;
        private int _totalCount;

        // 查询条件参数
        private DateTime? _startDate;
        private DateTime? _endDate;
        private List<int> _tagIds = new List<int>();

        /// <summary>
        /// 当前页码（最小值1）
        /// </summary>
        public int CurrentPage
        {
            get => _currentPage;
            set => _currentPage = Math.Max(value, 1);
        }

        /// <summary>
        /// 每页记录数（范围5-100）
        /// </summary>
        public int PageSize
        {
            get => _pageSize;
            set => _pageSize = Math.Clamp(value, 5, 100);
        }

        /// <summary>
        /// 总记录数（自动计算总页数）
        /// </summary>
        public int TotalCount
        {
            get => _totalCount;
            set => _totalCount = Math.Max(value, 0);
        }

        /// <summary>
        /// 总页数（根据总记录数和每页大小动态计算）
        /// </summary>
        public int TotalPages => (int)Math.Ceiling(_totalCount / (double)_pageSize);

        /// <summary>
        /// 设置时间范围过滤条件
        /// </summary>
        public void SetDateRange(DateTime? start, DateTime? end)
        {
            // 自动修正日期顺序
            if (start.HasValue && end.HasValue && start > end)
            {
                _startDate = end;
                _endDate = start;
            }
            else
            {
                _startDate = start;
                _endDate = end;
            }
        }

        /// <summary>
        /// 添加标签过滤条件（支持多标签联合查询）
        /// </summary>
        public void AddTags(IEnumerable<int> tagIds)
        {
            foreach (var id in tagIds)
            {
                if (id > 0 && !_tagIds.Contains(id))
                    _tagIds.Add(id);
            }
        }

        /// <summary>
        /// 生成分页查询SQL（使用参数化查询）
        /// </summary>
        /// <returns>
        /// Item1: 分页数据查询语句
        /// Item2: 总数统计查询语句
        /// Item3: 查询参数列表
        /// </returns>
        public (string dataQuery, string countQuery, Dictionary<string, object> parameters) BuildPagedQuery()
        {
            var parameters = new Dictionary<string, object>();
            var whereClause = new StringBuilder();
            var joinClause = new StringBuilder();

            // 构建标签过滤条件
            if (_tagIds.Count > 0)
            {
                joinClause.AppendLine("INNER JOIN bookmark_tags bt ON b.id = bt.bookmark_id");

                // 生成标签ID条件（避免SQL注入）
                var tagConditions = new List<string>();
                for (int i = 0; i < _tagIds.Count; i++)
                {
                    var paramName = $"@tagId{i}";
                    tagConditions.Add($"bt.tag_id = {paramName}");
                    parameters.Add(paramName, _tagIds[i]);
                }
                whereClause.Append($"({string.Join(" OR ", tagConditions)})");

                // 多标签需满足所有条件
                if (_tagIds.Count > 1)
                {
                    whereClause.Append($" GROUP BY b.id HAVING COUNT(DISTINCT bt.tag_id) = {_tagIds.Count}");
                }
            }

            // 构建时间范围条件
            if (_startDate.HasValue || _endDate.HasValue)
            {
                AddConditionPrefix(whereClause);

                if (_startDate.HasValue && _endDate.HasValue)
                {
                    whereClause.Append("b.created_time BETWEEN @startDate AND @endDate");
                    parameters.Add("@startDate", _startDate.Value);
                    parameters.Add("@endDate", _endDate.Value);
                }
                else if (_startDate.HasValue)
                {
                    whereClause.Append("b.created_time >= @startDate");
                    parameters.Add("@startDate", _startDate.Value);
                }
                else
                {
                    whereClause.Append("b.created_time <= @endDate");
                    parameters.Add("@endDate", _endDate.Value);
                }
            }

            // 构建完整查询语句
            var baseQuery = new StringBuilder()
                .AppendLine("FROM bookmarks b")
                .Append(joinClause);

            // 总数统计语句
            var countQuery = new StringBuilder()
                .Append("SELECT COUNT(DISTINCT b.id) ")
                .Append(baseQuery);

            if (whereClause.Length > 0)
                countQuery.Append(" WHERE ").Append(whereClause);

            // 分页数据查询语句
            var dataQuery = new StringBuilder()
                .AppendLine("SELECT")
                .AppendLine("  b.id,")
                .AppendLine("  b.url,")
                .AppendLine("  b.remark,")
                .AppendLine("  b.created_time,")
                .AppendLine("  GROUP_CONCAT(t.name, ', ') AS tags")
                .AppendLine("FROM bookmarks b")
                .AppendLine("LEFT JOIN bookmark_tags bt ON b.id = bt.bookmark_id")
                .AppendLine("LEFT JOIN tags t ON bt.tag_id = t.id")
                .Append(whereClause.Length > 0 ? " WHERE " : "")
                .AppendLine(whereClause.ToString())
                .AppendLine("GROUP BY b.id")
                .AppendLine($"ORDER BY b.created_time DESC LIMIT {_pageSize} OFFSET {(_currentPage - 1) * _pageSize}");

            return (dataQuery.ToString(), countQuery.ToString(), parameters);
        }

        /// <summary>
        /// 添加WHERE条件前缀（自动处理AND逻辑）
        /// </summary>
        private void AddConditionPrefix(StringBuilder whereClause)
        {
            if (whereClause.Length == 0)
                whereClause.Append(" WHERE ");
            else
                whereClause.Append(" AND ");
        }

        /// <summary>
        /// 验证分页参数有效性
        /// </summary>
        /// <exception cref="ArgumentException">无效参数时抛出</exception>
        public void ValidateParameters()
        {
            if (_currentPage < 1)
                throw new ArgumentException("页码不能小于1");

            if (_pageSize is < 5 or > 100)
                throw new ArgumentException("每页记录数需在5-100之间");

            if (_startDate > _endDate)
                throw new ArgumentException("开始时间不能晚于结束时间");
        }
    }
}